package com.jdone.compus.repository;

import com.jdone.compus.enums.ConsumptionStatus;
import com.jdone.compus.enums.OrderStatus;
import com.jdone.compus.model.StudentOrder;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;
import java.util.Optional;

public interface StudentOrderRepository extends JpaRepository<StudentOrder, Long> {
    Page<StudentOrder> findByStudentId(String studentId, Pageable pageable);

    Page<StudentOrder> findByStudentIdAndOrderStatusOrderByCreateTimeDesc(
            String studentId, OrderStatus orderStatus, Pageable pageable);

    Page<StudentOrder> findByStudentIdAndCreateTimeBetweenAndOrderStatusOrderByCreateTimeDesc(
            String studentId, LocalDateTime startDate, LocalDateTime endDate,
            OrderStatus orderStatus, Pageable pageable);

    Page<StudentOrder> findByStudentIdAndCreateTimeAfterAndOrderStatusOrderByCreateTimeDesc(
            String studentId, LocalDateTime startDate,
            OrderStatus orderStatus, Pageable pageable);

    Page<StudentOrder> findByStudentIdAndCreateTimeBeforeAndOrderStatusOrderByCreateTimeDesc(
            String studentId, LocalDateTime endDate,
            OrderStatus orderStatus, Pageable pageable);

    Optional<StudentOrder> findByBusinessOrderNo(String businessOrderNo);

    Iterable<StudentOrder> findByStudentId(String studentId);

    @Query("SELECT o FROM StudentOrder o WHERE (:startDateTime IS NULL OR o.createTime >= :startDateTime) " +
            "AND (:endDateTime IS NULL OR o.createTime <= :endDateTime) " +
            "AND (:consumptionStatus IS NULL OR o.consumptionStatus = :consumptionStatus) " +
            "AND (:businessOrderNo IS NULL OR o.businessOrderNo = :businessOrderNo) " +
            "ORDER BY o.createTime DESC")
    Page<StudentOrder> findByCreateTimeBetween(@Param("startDateTime") LocalDateTime startDateTime,
                                               @Param("endDateTime") LocalDateTime endDateTime,
                                               @Param("consumptionStatus") ConsumptionStatus consumptionStatus,
                                               @Param("businessOrderNo") String businessOrderNo,
                                               Pageable pageable);


    @Query(value = "SELECT COUNT(o.order_id) AS orderCount, COALESCE(SUM(o.order_amount), 0) AS totalAmount " +
            "FROM student_orders o " +
            "WHERE o.order_status = 'PAID' " +
            "AND o.consumption_status = 'CONSUMED' " +
            "AND (:startDateTime IS NULL OR o.create_time >= :startDateTime) " +
            "AND (:endDateTime IS NULL OR o.create_time <= :endDateTime)",
            nativeQuery = true)
    List<Map<String, Object>> getOrderStats(@Param("startDateTime") LocalDateTime startDateTime,
                                            @Param("endDateTime") LocalDateTime endDateTime);

}

